CREATE TABLE [dbo].[Nav_Menu]
(
[NavMenuID] [numeric] (18, 0) NOT NULL IDENTITY(1, 1),
[WorkflowStatusCode] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PublishedDateTime] [datetime] NULL,
[HideFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NavContentGroupInd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ReminderSentDateTime] [datetime] NULL,
[Title] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ParentNavMenuID] [numeric] (18, 0) NULL,
[AncestorNavMenuID] [numeric] (18, 0) NULL,
[CategoryDepth] [numeric] (18, 0) NULL,
[SortOrder] [numeric] (28, 18) NOT NULL,
[SecureFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TemplatePath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContentAuthorityGroupID] [numeric] (18, 0) NULL,
[AuthoritySetManuallyFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ContentID] [numeric] (18, 0) NULL,
[DirectListComboInd] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComponentCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComponentScriptCode] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComponentParameters] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ComponentParameterList] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OwnerContactID] [numeric] (18, 0) NULL,
[OwnerSetManuallyFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ExpirationDays] [numeric] (18, 0) NULL,
[ExpirationDate] [datetime] NULL,
[ExpSetManuallyFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LastUpdatedByContactID] [numeric] (18, 0) NULL,
[MembersOnlyFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShowInTopFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ShowInSideFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PublishedFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MicrositeFlag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DirectoryName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[WebsiteKey] [uniqueidentifier] NULL,
[PreFuseURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PostFuseURL] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PublishedDirectory] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PublishedDefaultFileName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FilePath] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AncestoryList] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DescendantCount] [int] NOT NULL CONSTRAINT [DF_Nav_Menu_DescendantCount] DEFAULT ((0)),
[PreviousWebsiteKey] [uniqueidentifier] NULL
) ON [PRIMARY]

GO
-- =============================================
-- Trigger on Nav_Menu to set...
--    - DirectoryName for new Nav_Menu records.
--
-- Modifications
-- 08/22/2003     E.Tatsui   Created
-- =============================================

CREATE TRIGGER [dbo].[asi_NavMenu_Insert]
    ON [dbo].[Nav_Menu]
    FOR INSERT
    AS
BEGIN
    DECLARE
    @NavMenuID numeric

    -- Get all the new records without DirectoryName, and set DirectoryName for each one.
    DECLARE c_NewNavMenu CURSOR FOR
        SELECT NavMenuID FROM inserted
        WHERE DirectoryName IS NULL

    OPEN c_NewNavMenu
    FETCH NEXT FROM c_NewNavMenu
        INTO @NavMenuID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC amsp_CMGetUniqueDirectoryName @NavMenuID, 1, NULL
        FETCH NEXT FROM c_NewNavMenu
            INTO @NavMenuID
    END
    CLOSE c_NewNavMenu
    DEALLOCATE c_NewNavMenu
END

GO
-- =============================================
-- Trigger on Nav_Menu table to set directory name and file path.
--
-- 10/11/2003    E.Tatsui    Created
-- =============================================

CREATE TRIGGER [dbo].[asi_NavMenu_Update]
    ON [dbo].[Nav_Menu]
    FOR UPDATE
AS
BEGIN
    DECLARE @NavMenuID numeric

    -- Get all the Nav_Menu records whose directory name has changed.
    IF UPDATE(DirectoryName)
    BEGIN
        DECLARE c_NavMenuUpdated CURSOR FOR
         SELECT a.NavMenuID
           FROM inserted a, deleted b
          WHERE a.NavMenuID = b.NavMenuID
            AND a.DirectoryName <> b.DirectoryName

        OPEN c_NavMenuUpdated
        FETCH NEXT FROM c_NavMenuUpdated
            INTO @NavMenuID

        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC amsp_CMUpdateNavProperties @NavMenuID

            FETCH NEXT FROM c_NavMenuUpdated
                INTO @NavMenuID
        END

        CLOSE c_NavMenuUpdated
        DEALLOCATE c_NavMenuUpdated
    END

    -- Set PreviousWebsiteKey when Website is changed, so that we can delete content from file system.
    IF UPDATE (WebsiteKey)
    BEGIN
        UPDATE Nav_Menu
           SET PreviousWebsiteKey = b.WebsiteKey
          FROM inserted a, deleted b
         WHERE Nav_Menu.NavMenuID = a.NavMenuID
           AND a.NavMenuID = b.NavMenuID
           AND a.WebsiteKey <> b.WebsiteKey
           AND a.PreviousWebsiteKey IS NULL

        UPDATE Nav_Menu
           SET PreviousWebsiteKey = NULL
          FROM inserted a
         WHERE a.WebsiteKey = a.PreviousWebsiteKey
           AND Nav_Menu.NavMenuID = a.NavMenuID
    END
END

GO
ALTER TABLE [dbo].[Nav_Menu] ADD CONSTRAINT [PK_Nav_Menu] PRIMARY KEY CLUSTERED ([NavMenuID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Nav_Menu] ADD CONSTRAINT [ak_nav_menu_name] UNIQUE NONCLUSTERED ([Name]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Nav_Menu] ADD CONSTRAINT [ak_nav_menu_sortorder] UNIQUE NONCLUSTERED ([SortOrder]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_Nav_Menu_2] ON [dbo].[Nav_Menu] ([AncestorNavMenuID], [NavContentGroupInd], [SortOrder]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Nav_Menu_ComponentCode] ON [dbo].[Nav_Menu] ([ComponentCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Nav_Menu_ComponentScriptCode] ON [dbo].[Nav_Menu] ([ComponentScriptCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Nav_Menu_ContentAuthorityGroupID] ON [dbo].[Nav_Menu] ([ContentAuthorityGroupID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Nav_Menu_ContentID] ON [dbo].[Nav_Menu] ([ContentID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Nav_Menu_LastUpdatedByContactID] ON [dbo].[Nav_Menu] ([LastUpdatedByContactID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Nav_Menu_OwnerContactID] ON [dbo].[Nav_Menu] ([OwnerContactID]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [ix_Nav_Menu_1] ON [dbo].[Nav_Menu] ([ParentNavMenuID], [NavContentGroupInd], [SortOrder]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Nav_Menu_WebsiteKey] ON [dbo].[Nav_Menu] ([WebsiteKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Nav_Menu_WorkflowStatusCode] ON [dbo].[Nav_Menu] ([WorkflowStatusCode]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Nav_Menu] ADD CONSTRAINT [FK_Nav_Menu_Website] FOREIGN KEY ([WebsiteKey]) REFERENCES [dbo].[Website] ([WebsiteKey])
GO
ALTER TABLE [dbo].[Nav_Menu] ADD CONSTRAINT [FK_NavMenu_ComponentScript] FOREIGN KEY ([ComponentCode], [ComponentScriptCode]) REFERENCES [dbo].[Component_Script_Ref] ([ComponentCode], [ComponentScriptCode])
GO
ALTER TABLE [dbo].[Nav_Menu] ADD CONSTRAINT [FK_NavMenu_Content] FOREIGN KEY ([ContentID]) REFERENCES [dbo].[Content] ([ContentID])
GO
ALTER TABLE [dbo].[Nav_Menu] ADD CONSTRAINT [FK_NavMenu_ContentAuthGroup] FOREIGN KEY ([ContentAuthorityGroupID]) REFERENCES [dbo].[Content_Authority_Group] ([ContentAuthorityGroupID])
GO
ALTER TABLE [dbo].[Nav_Menu] ADD CONSTRAINT [FK_NavMenu_Producer] FOREIGN KEY ([OwnerContactID]) REFERENCES [dbo].[Producer] ([ContactID])
GO
ALTER TABLE [dbo].[Nav_Menu] ADD CONSTRAINT [FK_NavMenu_Producer2] FOREIGN KEY ([LastUpdatedByContactID]) REFERENCES [dbo].[Producer] ([ContactID])
GO
ALTER TABLE [dbo].[Nav_Menu] ADD CONSTRAINT [FK_NavMenu_WorkflowStatus] FOREIGN KEY ([WorkflowStatusCode]) REFERENCES [dbo].[Workflow_Status_Ref] ([WorkflowStatusCode])
GO
GRANT REFERENCES ON  [dbo].[Nav_Menu] TO [IMIS]
GRANT SELECT ON  [dbo].[Nav_Menu] TO [IMIS]
GRANT INSERT ON  [dbo].[Nav_Menu] TO [IMIS]
GRANT DELETE ON  [dbo].[Nav_Menu] TO [IMIS]
GRANT UPDATE ON  [dbo].[Nav_Menu] TO [IMIS]
GO